Rendering PostGIS Raster graphics with LibreOffice Base Reports   

Connecting to a PostgreSQL database

As said the native PostgreSQL driver in 3.5 just uses the SDBC syntax, so I was able to connect to my database by typing this in for the Database URL:

host=localhost port=5440 dbname=postgis20_sampler

If you want to not be prompted for username and password, for newer odb, seems you have to include in the connection string like so.

host=localhost port=5440 dbname=postgis20_sampler user=postgres password=whatever

My port is pretty non-standard since I've got like 20 different versions of PostgreSQL installed on my local pc for development. Your port would most likely be 5432.

Using Ad-Hoc queries that return images in Base

What I really wanted to do was render a randomly complex query that returns images like this one.

SELECT foo.id, ST_AsPNG(
   ST_AsRaster(
   CASE WHEN id = 1 THEN 
    ST_Buffer(
      ST_MakeLine(
          ST_Translate(
              ST_Transform(foo.geom,26986),x*random()*500,y*random()*500)
              )
              ,2)  
   ELSE
             ST_ConcaveHull(
      ST_MakeLine(
          ST_Translate(
              ST_Transform(foo.geom,26986),x*random()*500,y*random()*500)
              ),0.90) 
              
   END
    , 200,200,ARRAY['8BUI', '8BUI', '8BUI'], ARRAY[100*foo.id,154,118], ARRAY[0,0,0])
    ) As png_img
FROM (VALUES (1,  ST_GeomFromText('POINT(-71.124 42.2395)',4326)  )
     , (2,  ST_GeomFromText('POINT(-71.555 42.4325)',4326) )
      ) AS foo(id, geom) 
      CROSS JOIN generate_series(1,10) As x 
      CROSS JOIN generate_series(1,20) As y
GROUP BY foo.id;

So steps:

1. Select Reports and in Task choose Create Report in Design View as shown in:
2. Pick SQL Command from Data Content tab. If you don't see Data content tab, make sure to click the grey area right below the Page footer in the design view
3. Next click the ... next to the Content, which will pop open a designer with tables to pick etc, close the table dialog. Real database programmers don't like being constrained by query designers and want to write real SQL , so go to the view menu and Switch Design View off as shown here . If you are not a real database programmer, just humor me and pretend you are.
4. Cut and paste your finely crafted SQL masterpiece into the blank window
5. UPDATE: In LibreOffice 4.0 they introduced an SQL Parser which prevents you from writing some advanced queries such as ones involving CTEs. This particular query is one that confounds the parser and it marks as invalid.
   Turn off the parser by File->Edit->Run SQL command directly
6. click the run as SQL command directly SQL icon and then the green save icon.
7. If your SQL is valid, you should see a list of columns from your query like this:
8. Drag the fields on the design screen and fuss with them. After fussing, I got a design view like
9. Click the which produced an output that can also be output as pdf or html page.

It's not my best work, but proves the point that yes you can write arbitrarily complex SQL even involving queries that output images and have LibreOffice base render them.